package com.oisix.oisystemec.dao;

import java.math.BigDecimal;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.EntityMode;
import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.CriteriaSpecification;

import com.oisix.oisystemec.model.BlackKokyakuCondition;
import com.oisix.oisystemec.model.Kokyaku;
import com.oisix.oisystemec.model.Tantousha;
import com.oisix.oisystemec.util.SiteCode;
import com.oisix.oisystemfr.persistence.EntityManager;
import com.oisix.oisystemfr.persistence.EntityManagerFactory;
import com.oisix.oisystemfr.util.DateUtil;
import com.oisix.oisystemfr.util.StringUtil;
/**
 * @author HopND
 * @version 1.0
 */
public class KokyakuDao {
    private static final int MAX_PARAMETER = 1000;

    /**
     * find kokyaku by Id.
     * @param kokyakuId
     * @return object kokyaku.
     */
    public static Kokyaku get(String kokyakuId) {
        if (kokyakuId == null) {
            return null;
        }
        EntityManager em = EntityManagerFactory.getEntityManager();
        Kokyaku kokyaku = em.find(Kokyaku.class, kokyakuId);
        return kokyaku;
    }

    /**
     * search kokyaku by shimei, email, tel, todoufukenId.
     * @param shimei
     * @param email
     * @param tel
     * @param todoufukenId
     * @param addr
     * @param keitaiMail
     * @param pageSize
     * @param pageIndex
     * @return List<kokyaku>.
     */
    @SuppressWarnings("unchecked")
    public static List<Kokyaku> getKokyaku(
            String shimei,
            String email,
            String tel,
            BigDecimal todoufukenId,
            String addr,
            String keitaiMail,
            BigDecimal gochiFlg,
            int pageSize,
            int pageIndex) {
        StringBuilder hql = new StringBuilder();
        hql.append("from Kokyaku as k where k.sakujoFlg = 0");
        addSeachCondition(shimei, email, tel,
                          todoufukenId, addr, keitaiMail, gochiFlg, hql);
        hql.append(" order by kokyakuId");
        EntityManager em = EntityManagerFactory.getEntityManager();
        Query query = em.createQuery(hql.toString());
        query.setMaxResults(pageSize);
        query.setFirstResult(pageSize * pageIndex);
        addValueCondtion(shimei, email, tel,
                         todoufukenId, addr, keitaiMail, gochiFlg, query);
        return query.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list();
    }
    /**
     * Get list of Kokyaku base on Kokyaku Id list
     * @param kokyakuIds
     * @param gochiFlg
     * @param pageIndex is -1 if dont need paging
     * @param pageSize is -1 if dont need paging
     * @return
     */
    @SuppressWarnings("unchecked")
    public static List<Kokyaku> getByIds(List<String> kokyakuIds,
            BigDecimal gochiFlg, int pageIndex, int pageSize) {
        EntityManager em = EntityManagerFactory.getEntityManager();
        List<String> subList;
        int numOfPage = kokyakuIds.size() / pageSize;
        if (kokyakuIds.size() % pageSize != 0) {
            numOfPage++;
        }
        numOfPage--;
        if (pageIndex == numOfPage) {
            subList = kokyakuIds.subList(pageIndex * pageSize, pageIndex
                    * pageSize
                    + ((pageIndex == 0) ? kokyakuIds.size() : (((kokyakuIds
                            .size()
                            % pageSize == 0) ? pageSize : kokyakuIds.size()
                            % pageSize))));
        } else {
            subList = kokyakuIds.subList(pageIndex * pageSize, (pageIndex + 1)
                    * pageSize);
        }
        StringBuilder hql = new StringBuilder("from Kokyaku k");
        hql.append(" where k.kokyakuId in (:kokyakuId) ");
        if (gochiFlg != null) {
            hql.append(" and k.gochiFlg = :gochiFlg ");
        }
        hql.append(" and k.sakujoFlg = 0 order by kokyakuId");
        Query query = em.createQuery(hql.toString());
        query.setParameterList("kokyakuId", subList);
        if (gochiFlg != null) {
            query.setBigDecimal("gochiFlg", gochiFlg);
        }
        return query.list();
    }

    /**
     * Get number of Kokyaku base on kokyaku id list
     * @param kokyakuIds
     * @param gochiFlg
     * @return
     */
    public static Long getByIdsCount(
            List<String> kokyakuIds, BigDecimal gochiFlg) {
        EntityManager em = EntityManagerFactory.getEntityManager();
        List<String> subList;
        long kCount = 0;
        int numOfPage = kokyakuIds.size() / MAX_PARAMETER;
        if(kokyakuIds.size() % MAX_PARAMETER != 0) {
            numOfPage++;
        }
        numOfPage--;
        StringBuilder hql = new StringBuilder(
                " select count(*) as totalkokyaku from Kokyaku k");
        hql.append(" where k.kokyakuId in (:kokyakuId) ");
        if (gochiFlg != null) {
            hql.append(" and k.gochiFlg = :gochiFlg ");
        }
        hql.append(" and k.sakujoFlg = 0 order by kokyakuId");
        for (int i = 0; i < numOfPage + 1; i++) {
            if (i == numOfPage) {
                subList = kokyakuIds.subList(i * MAX_PARAMETER, i
                        * MAX_PARAMETER
                        + ((i == 0) ? kokyakuIds.size() : (((kokyakuIds.size()
                                % MAX_PARAMETER == 0) ? MAX_PARAMETER
                                : kokyakuIds.size() % MAX_PARAMETER))));
            } else {
                subList = kokyakuIds.subList(i * MAX_PARAMETER, (i + 1)
                        * MAX_PARAMETER);
            }
            Query query = em.createQuery(hql.toString());
            query.setParameterList("kokyakuId", subList);
            if (gochiFlg != null) {
                query.setBigDecimal("gochiFlg", gochiFlg);
            }
            kCount += (Long) query.uniqueResult();
            em.clear();
        }
        return kCount;
    }

    /**
     * Adding search condition.
     * @param shimei
     * @param email
     * @param tel
     * @param todoufukenId
     * @param addr
     * @param keitaiMail
     * @param gochiFlg
     * @param query
     */
    private static void addValueCondtion(
            String shimei,
            String email,
            String tel,
            BigDecimal todoufukenId,
            String addr,
            String keitaiMail,
            BigDecimal gochiFlg,
            Query query) {
        // If exist shimei set search by shimei
        if (!StringUtils.isEmpty(shimei)) {
            query.setString("shimei", '%' + shimei.trim() + '%');
        }
        // If exist email then set by email
        if (!StringUtils.isEmpty(email)) {
            query.setString("email", '%' + email.trim() + '%');
        }
        // If exist telephone then set by telephone
        if (!StringUtils.isEmpty(tel)) {
            query.setString("tel", tel.trim().replace("-", ""));
        }
        // If exist todoufuken then set by todoufuken
        if (todoufukenId.intValue() >= 0) {
            query.setBigDecimal("todoufukenId", todoufukenId);
        }
        // If exist address then set by address
        if (!StringUtils.isEmpty(addr)) {
            query.setString("addr", '%' + addr.trim() + '%');
        }
        // If exist keitail Emal then set by keitail email
        if (!StringUtils.isEmpty(keitaiMail)) {
            query.setString("keitaiMail", '%' + keitaiMail.trim() + '%');
        }
        if (gochiFlg != null && !Tantousha.TANTOUSHA_KUBUN_ALL.equals(gochiFlg)){
            query.setBigDecimal("gochiFlg", gochiFlg);
        }
    }

    /**
     * Set value for search condition.
     * @param shimei
     * @param email
     * @param tel
     * @param todoufukenId
     * @param addr
     * @param keitaiMail
     * @param gochiFlg
     * @param hql
     */
    private static void addSeachCondition(
            String shimei,
            String email,
            String tel,
            BigDecimal todoufukenId,
            String addr,
            String keitaiMail,
            BigDecimal gochiFlg,
            StringBuilder hql) {
     // If exist todoufuken then search by todoufuken
        if (todoufukenId.intValue() >= 0) {
            hql.append(" and k.todoufukenId= :todoufukenId");
        }
     // If exist telephone then search by telephone
        if (!StringUtils.isEmpty(tel)) {
            hql.append(" and trim(replace(k.tel,'-','')) = :tel");
        }
        // If exist shimei then search by shimei
        if (!StringUtils.isEmpty(shimei)) {
            hql.append(" and k.shimei like :shimei");
        }
        // If exist email then search by email
        if (!StringUtils.isEmpty(email)) {
            hql.append(" and k.eMail like :email");
        }
        // If exist keitail Emal then search by keitail email
        if (!StringUtils.isEmpty(keitaiMail)) {
            hql.append(" and k.keitaiMail like :keitaiMail");
        }
     // If exist address then search by address
        if (!StringUtils.isEmpty(addr)) {
            hql.append(" and ((k.addr1 || k.addr2) like :addr)");
        }
        if (gochiFlg != null && !Tantousha.TANTOUSHA_KUBUN_ALL.equals(gochiFlg)){
            hql.append(" and k.gochiFlg = :gochiFlg ");
        }
    }

    /**
     * Count number of kokyaku by search input condition.
     * @param shimei
     * @param email
     * @param tel
     * @param todoufukenId
     * @param addr
     * @param keitaiMail
     * @param gochiFlg
     * @return number of records corresponding search condition
     */
    public static Long getKokyakuCount(
            String shimei,
            String email,
            String tel,
            BigDecimal todoufukenId,
            String addr,
            String keitaiMail,
            BigDecimal gochiFlg) {
        StringBuilder hql = new StringBuilder();
        hql.append("select count(*) as totalkokyaku from Kokyaku as k ");
        hql.append("where k.sakujoFlg = 0");
        addSeachCondition(shimei, email, tel,
                          todoufukenId, addr, keitaiMail,gochiFlg, hql);
        EntityManager em = EntityManagerFactory.getEntityManager();
        Query query = em.createQuery(hql.toString());
        addValueCondtion(shimei, email, tel,
                          todoufukenId, addr, keitaiMail, gochiFlg, query);
        Object result = query.uniqueResult();
        if (result == null) {
            return 0L;
        }
        return Long.parseLong(result.toString());
    }

    /**
     * update Kukyaku.
     * @param kokyaku
     */
    public static void update(Kokyaku kokyaku) {
        EntityManager em = EntityManagerFactory.getEntityManager();
        em = em.getSecondaryEntity(EntityMode.POJO);
        em.persist(kokyaku);
    }

    /**
     * CAP会員ID元に顧客情報を取得する。<br>
     * isActive = true  の場合は削除フラグ = 0 データのみ<br>
     * isActive = false の場合は削除フラグを無視して取得します
     * @param capMemberId
     * @param active
     * @return
     */
    public static Kokyaku getByCapMemberId(String capMemberId, boolean isActive) {
        if (StringUtils.isEmpty(capMemberId)) {
            return null;
        }
        StringBuilder hql = new StringBuilder();
        hql.append("from ").append(Kokyaku.class.getName()).append(" t1 ");
        hql.append("where t1.gochiFlg = 1 ");
        hql.append("and t1.capFlg = 1 ");
        hql.append("and t1.capMemberId = :capMemberId ");
        if (isActive) {
            hql.append("and t1.sakujoFlg = 0");
        }
        EntityManager em = EntityManagerFactory.getEntityManager();
        Query query = em.createQuery(hql.toString());
        query.setString("capMemberId", capMemberId);
        return (Kokyaku)query.uniqueResult();
    }

    /**
     * 2011-09-30 HaTQ
     * Get list of kokyaku_id by condition.
     * @param kokyakuShimei
     * @param kokyakuMail
     * @param kokyakuTel
     * @param gochiFlg
     * @return list of kokyaku_id
     */
    @SuppressWarnings("unchecked")
    public static List<String> getKokyakuIdList(String kokyakuShimei
                                                , String kokyakuMail
                                                , String kokyakuTel){
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT kokyaku_id ");
        sql.append("FROM M_KOKYAKU kokyaku ");
        sql.append("WHERE kokyaku.sakujo_flg = 0 ");
        if(0 < kokyakuShimei.length()){
            sql.append("    AND kokyaku.shimei LIKE :kokyakuShimei ");
        }
        if(0 < kokyakuMail.length()){
            sql.append("    AND kokyaku.e_mail LIKE :kokyakuMail ");
        }
        if(0 < kokyakuTel.length()){
            sql.append("    AND REPLACE(kokyaku.tel,'-','') ");
            sql.append("        LIKE :kokyakuTel ");
        }
        sql.append("        AND kokyaku.gochi_flg=:gochiFlg ");
        sql.append("ORDER BY kokyaku.kokyaku_id DESC");

        EntityManager em = EntityManagerFactory.getEntityManager();
        SQLQuery query = em.createSQLQuery(sql.toString());
        if(0 < kokyakuShimei.length()){
            query.setString("kokyakuShimei", "%" + kokyakuShimei + "%");
        }
        if(0 < kokyakuMail.length()){
            query.setString("kokyakuMail", "%" + kokyakuMail + "%");
        }
        if(0 < kokyakuTel.length()){
            query.setString("kokyakuTel", "%" + kokyakuTel.replace("-", "") + "%");
        }
        query.setInteger("gochiFlg", SiteCode.getSiteCode());

        query.addScalar("kokyaku_id", Hibernate.STRING);
        List<Map<String, String>> idList = query.setResultTransformer(
                           CriteriaSpecification.ALIAS_TO_ENTITY_MAP).list();

        List<String> kokyakuIdList = new ArrayList<String>();
        if (0 < idList.size()) {
            for (Map<String, String> kokyakuIdObj : idList) {
                kokyakuIdList.add(kokyakuIdObj.get("kokyaku_id"));
            }
        }

        return kokyakuIdList;
    }

    /**
     * 2012/04/11 TungTDH
     * Find kokyaku model by kokyakuId.
     *
     * @param kokyakuId
     * @return Kokyaku - null if sakujoFlg <> 0 or kokyakuId is null.
     */
    public static Kokyaku findById(String kokyakuId) {
        if (kokyakuId == null) {
            return null;
        }
        StringBuilder hql = new StringBuilder();
        hql.append("from Kokyaku as k where k.sakujoFlg = 0");
        hql.append("AND k.kokyakuId = :kokyakuId");
        EntityManager em = EntityManagerFactory.getEntityManager();
        Query query = em.createQuery(hql.toString());
        query.setString("kokyakuId", kokyakuId);
        Kokyaku result = (Kokyaku) query.uniqueResult();
        return result;
    }

    /**
     * @param Kokyaku
     * @param em
     * @return List<Kokyaku>
     */
    @SuppressWarnings("unchecked")
    public static List<Kokyaku> searchById( Kokyaku kokyaku, EntityManager em ) {
        StringBuffer sql = new StringBuffer();
        sql.append("FROM ");
        sql.append(Kokyaku.class.getName());
        sql.append(" WHERE");
        sql.append("   sakujo_flg = '0'");
        sql.append("   AND gochi_flg = :gochiFlg");
        sql.append("   AND (");
        sql.append("    e_mail = :eMail");
        sql.append("    OR keitai_mail = :keitaiMail");
        sql.append("    OR (");
        sql.append("     shimei = :shimei");
        sql.append("     AND TRIM( REPLACE( tel, '-', '' ) ) = :tel");
        sql.append("    )");
        sql.append("   )");
        sql.append(" ORDER BY");
        sql.append("  saishuukoushin_date DESC");
        Query query = em.createQuery(sql.toString());
        query.setBigDecimal("gochiFlg", kokyaku.getGochiFlg());
        String shimei = kokyaku.getShimei();
        String tel = StringUtils.trim( StringUtils.replace( kokyaku.getTel(),"-","") );
        query.setString("shimei", shimei );
        query.setString("tel", tel );
        query.setString("eMail", kokyaku.getEMail());
        query.setString("keitaiMail", kokyaku.getKeitaiMail());
        return (List<Kokyaku>)query.list();
    }

    /**
     * PCメールアドレスから顧客を取得する。
     * @param eMail    PCメールアドレス
     * @return    顧客
     */
    @SuppressWarnings("unchecked")
    public static List<Kokyaku> getKokyakuListByEMail(String eMail) {

        if ( eMail == null ) {
            return ( null );
        }

        String hql = "from Kokyaku as k where k.eMail = :eMail and k.sakujoFlg = 0";

        EntityManager em = EntityManagerFactory.getEntityManager();
        Query query = em.createQuery(hql);
        query.setString("eMail", eMail);

        return (List<Kokyaku>)query.list();
    }

    /**
     * 携帯メールアドレスから顧客リストを取得する。
     * @param keitaiMail    携帯メールアドレス
     * @return    顧客リスト
     */
    public static List<Kokyaku> getKokyakuListByKeitaiMail(String keitaiMail) {

        if ( keitaiMail == null ) {
            return ( null );
        }

        String hql = "from Kokyaku as k where k.keitaiMail = :keitaiMail and k.sakujoFlg = 0";

        EntityManager em = EntityManagerFactory.getEntityManager();
        Query query = em.createQuery(hql);
        query.setString("keitaiMail", keitaiMail);

        return ( (List<Kokyaku>)query.list() );
    }

    /**
     * PCメールアドレスから顧客を取得する。
     * @param eMail    PCメールアドレス
     * @return    顧客
     */
    public static Kokyaku getByEMail(String eMail) {

        if ( eMail == null ) {
            return ( null );
        }

        String hql = "from Kokyaku as k where k.eMail = :eMail and k.sakujoFlg = 0";

        EntityManager em = EntityManagerFactory.getEntityManager();
        Query query = em.createQuery(hql);
        query.setString("eMail", eMail);

        return ( (Kokyaku)query.uniqueResult() );
    }

    /**
     * 氏名と電話番号から顧客を取得する。
     * @param name    氏名
     * @param tel    電話番号
     * @return    顧客
     */
    public static List<Kokyaku> getByShimeiAndTel(String name, String tel) {

        if ( name == null || tel == null ) {
            return ( null );
        }

        String hql = "from Kokyaku as k " +
                     "where replace(replace(k.shimei, ' ', ''), '　', '') = :name " +
                     "and trim(replace(k.tel, '-', '')) = :tel " +
                     "and k.sakujoFlg = 0";

        EntityManager em = EntityManagerFactory.getEntityManager();
        Query query = em.createQuery(hql);
        query.setString("name", name.replaceAll(" |　", ""));
        query.setString("tel", tel.replaceAll("-", ""));

        List list = query.list();
        if (list != null && list.size() > 0) {
            return list;
        } else {
            return null;
        }
    }
    /**
     * 2012/10/11 HungNM create.
     * Get list of customer need to balance point.
     * @return
     */
    @SuppressWarnings("unchecked")
    public static List<Map<String, Object>> getPointBalance() {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT ");
        sql.append("  TRIM(kokyaku_id) AS kokyakuId, ");
        sql.append("  TRIM(shimei) AS shimei, ");
        sql.append("  TRIM(furigana) AS furigana, ");
        sql.append("  pointo AS pointo, ");
        sql.append("  TO_CHAR(shinkisakusei_date, ");
        sql.append("          'YYYY/MM/DD HH24:MI:SS') AS shinkisakuseiDate, ");
        sql.append("  gochi_flg AS gochiFlg ");
        sql.append("FROM m_kokyaku ");
        sql.append("WHERE ");
        sql.append("  torihikisaki_id <> 17 ");
        sql.append("  AND torihikisaki_id <> 19 ");
        sql.append("  AND pointo > 0 ");

        EntityManager em = EntityManagerFactory.getEntityManager();
        SQLQuery query = em.createSQLQuery(sql.toString());
        query.addScalar("kokyakuId");
        query.addScalar("shimei");
        query.addScalar("furigana");
        query.addScalar("pointo");
        query.addScalar("shinkisakuseiDate");
        query.addScalar("gochiFlg");

        List<Map<String, Object>> list = query.setResultTransformer(
                CriteriaSpecification.ALIAS_TO_ENTITY_MAP).list();

        return list;
    }
    
    /**
     * 2014/08/7 AOMSINh create.
     * Get summary list of customer need to balance point.
     * @return
     */
    @SuppressWarnings("unchecked")
    public static List<Map<String, Object>> getPointSummaryBalance() {
        StringBuffer sql = new StringBuffer();

        sql.append("SELECT ");
        sql.append(" SUM(pointo) AS totalPoint, ");
        sql.append("FROM M_KOKYAKU ");
        sql.append("WHERE ");
        sql.append("  torihikisaki_id <> 17 ");
        sql.append("  AND torihikisaki_id <> 19 ");
        sql.append("  AND pointo > 0 ");        
        

        EntityManager em = EntityManagerFactory.getEntityManager();
        SQLQuery query = em.createSQLQuery(sql.toString());
        query.addScalar("totalPoint");

        List<Map<String, Object>> list = query.setResultTransformer(
                CriteriaSpecification.ALIAS_TO_ENTITY_MAP).list();

        return list;
    }

    /**
     * 2012/10/05 HuongTD1 create.
     * Get list of customer (or not) to send manma mail.
     * @param isVipOnly
     * @param isLastWeek
     * @param siteCode
     * @return taishoushaList
     */
    @SuppressWarnings("unchecked")
    public static List<Map<String,Object>> getSendManmaMail(
            Timestamp processDate, boolean isVipOnly, boolean isLastWeek) {
        Date taishouStart = DateUtil.toDate(processDate);
        Date taishouEnd;
        int dayOfWeek = DateUtil.getYoubi_id(taishouStart);
        if (Calendar.THURSDAY == dayOfWeek) {
            taishouEnd = DateUtil.getMoveDay(3, taishouStart);
        } else {
            taishouEnd = taishouStart;
        }

        StringBuilder sql = new StringBuilder();
        sql.append("SELECT");
        sql.append("        TRIM(juchuu.kokyaku_id) AS kokyakuId");
        sql.append("        ,kokyaku.e_mail AS eMail");
        sql.append("        ,kokyaku.keitai_mail AS keiTaiMail");
        sql.append("        ,kokyaku.shimei AS shimei");
        sql.append("        ,TRIM(juchuu.juchuu_id) AS tmmjuchuuId");
        sql.append("        ,juchuu.hacchuuyotei_date AS hacchuuyoteiDate");
        sql.append("        ,juchuu.toutyakuyotei_date AS toutyakuyoteiDate");
        sql.append("    FROM");
        sql.append("        (");
        sql.append("            SELECT");
        sql.append("                    *");
        sql.append("                FROM");
        sql.append("                    tbl_juchuu");
        sql.append("                WHERE");
        sql.append("                    juchuushubetsu = 1");
        sql.append("                    AND gochi_flg = :siteCode");
        sql.append("                    AND hacchuuyotei_date >= TO_DATE(:taishouStart ,'YYYY/MM/DD HH24/MI/SS')");
        sql.append("                    AND hacchuuyotei_date <= TO_DATE(:taishouEnd ,'YYYY/MM/DD HH24/MI/SS')");
        sql.append("                    AND teiki_ind > 0");
        sql.append("                    AND sakujo_flg = 0");
        sql.append("                    AND juchuu_date = shinkisakusei_date");
        sql.append("                    AND (");
        sql.append("                        manma_flg = 0");
        sql.append("                        OR manma_flg is null");
        sql.append("                    )");
        sql.append("        ) juchuu INNER JOIN (");
        sql.append("            SELECT");
        sql.append("                    kokyaku_id");
        sql.append("                    ,shimei");
        sql.append("                    ,e_mail");
        sql.append("                    ,keitai_mail");
        sql.append("                FROM");
        sql.append("                    m_kokyaku");
        sql.append("                WHERE");
        sql.append("                    sakujo_flg = 0");
        sql.append("                    AND gochi_flg = :siteCode");
        if (isVipOnly) {
            sql.append("                    AND vip_flg = 1");
        }
        sql.append("        ) kokyaku");
        sql.append("            ON (juchuu.kokyaku_id = kokyaku.kokyaku_id)");

        EntityManager em = EntityManagerFactory.getEntityManager();
        SQLQuery query = em.createSQLQuery(sql.toString());
        query.setString("taishouStart",
                DateUtil.toString(taishouStart, "yyyy/MM/dd") + " 00/00/00");
        query.setString("taishouEnd",
                DateUtil.toString(taishouEnd, "yyyy/MM/dd" + " 23/59/59"));
        query.setInteger("siteCode", SiteCode.getSiteCode());

        query.addScalar("kokyakuId");
        query.addScalar("eMail");
        query.addScalar("keiTaiMail");
        query.addScalar("shimei");
        query.addScalar("tmmjuchuuId");
        query.addScalar("hacchuuyoteiDate");
        query.addScalar("toutyakuyoteiDate");

        List<Map<String, Object>> list = query.setResultTransformer(
                            CriteriaSpecification.ALIAS_TO_ENTITY_MAP).list();

        return list;
    }

    /**
     * 2012/08/01 QuynhNX create.
     * get customer name.
     */
    public static String getCustomerName(String kokyakuId) {
        EntityManager em = EntityManagerFactory.getEntityManager();
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT ");
        sql.append("    shimei AS shimei ");
        sql.append(" FROM ");
        sql.append("     m_kokyaku ");
        sql.append(" WHERE ");
        sql.append("    kokyaku_id = :kokyakuId ");
        SQLQuery query = em.createSQLQuery(sql.toString());
        query.setString("kokyakuId", kokyakuId);
        query.addScalar("shimei", Hibernate.STRING);

        return query.list().size() > 0 ? query.list().get(0).toString() : null;
    }

    /**
     * 2012/11/21 QuynhNX create.
     * get customer gochi.
     */
    public static String getGochiCustomer(String kokyakuId) {
        EntityManager em = EntityManagerFactory.getEntityManager();
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT ");
        sql.append("    gochi_flg AS gochiFlg ");
        sql.append(" FROM ");
        sql.append("     m_kokyaku ");
        sql.append(" WHERE ");
        sql.append("    kokyaku_id = :kokyakuId ");
        SQLQuery query = em.createSQLQuery(sql.toString());
        query.setString("kokyakuId", kokyakuId);
        query.addScalar("gochiFlg", Hibernate.INTEGER);

        return query.list().size() > 0 ? query.list().get(0).toString() : null;
    }



    public static int updateTorihikisakiIdForLinkShare() {

        EntityManager em = EntityManagerFactory.getEntityManager();

        StringBuffer sb = new StringBuffer();
        sb.append("UPDATE");
        sb.append("        m_kokyaku kokyaku");
        sb.append("    SET");
        sb.append("        kokyaku.torihikisaki_id = 635");
        sb.append("    WHERE");
        sb.append("        kokyaku.kokyaku_id IN (");
        sb.append("                SELECT");
        sb.append("                        juchuu.kokyaku_id");
        sb.append("                    FROM");
        sb.append("                        tbl_juchuu juchuu");
        sb.append("                        ,tbl_linkshare ls");
        sb.append("                    WHERE");
        sb.append("                        juchuu.juchuu_id = ls.juchuu_id");
        sb.append("                        AND ls.kotaishikibetu_id IS null");
        sb.append("                        AND ls.keitaikishu_info IS null");
        sb.append("                        AND juchuu.juchuukaisuu = 1");
        sb.append("        )");
        sb.append("        AND (");
        sb.append("            torihikisaki_id = 0");
        sb.append("            OR torihikisaki_id IS null");
        sb.append("        )");

        SQLQuery query = em.createSQLQuery(sb.toString());
        return query.executeUpdate();
    }


    /**
     * Count number of black kokyaku by search input condition.
     * 
     * @param List of condition item used to acquire the number of cases
     * @return number of records corresponding search condition
     */
    public static Long getBlackKokyakuCount(List<BlackKokyakuCondition> conditionList) {

        StringBuilder sql = new StringBuilder();

        sql.append("SELECT ")
            .append("    COUNT(1) AS totalblackkokyaku ")
            .append(KokyakuDao.getBlackKokyakuStaticCondition());

        SQLQuery query = KokyakuDao.getBlackKokyakuSqlQuery(sql.toString(), conditionList);
        List<?> list = query.list();

        return Long.parseLong(String.valueOf(list.get(0)));
    }

    /**
     * List of black kokyaku by search input condition.
     * 
     * @param List of condition item used to acquire list of black kokyaku
     * @return list of records corresponding search condition
     */
    @SuppressWarnings("unchecked")
    public static List<Map<String, String>> getBlackKokyaku(
            List<BlackKokyakuCondition> conditionList) {

        StringBuilder sql = new StringBuilder();

        sql.append("SELECT DISTINCT ")
            .append("    T2.KOKYAKU_ID, ")
            .append("    T2.TODOUFUKEN_ID, ")
            .append("    T3.TODOUFUKEN, ")
            .append("    T2.ADDR1, ")
            .append("    T2.SHIMEI, ")
            .append("    T2.TEL, ")
            .append("    T2.TODOKESAKI_IND, ")
            .append("    TO_CHAR(T2.SHINKISAKUSEI_DATE,")
            .append("            'yyyy/mm/dd hh24:mi:ss') AS SHINKISAKUSEI_DATE, ")
            .append("    T1.SAKUJO_FLG, ")
            .append("    T1.CHUUI1, ")
            .append("    T1.CHUUI2, ")
            .append("    T1.CHUUI3 ")
            .append(KokyakuDao.getBlackKokyakuStaticCondition());

        SQLQuery query = KokyakuDao.getBlackKokyakuSqlQuery(sql.toString(), conditionList);
        query.addScalar("KOKYAKU_ID", Hibernate.STRING);
        query.addScalar("TODOUFUKEN_ID", Hibernate.STRING);
        query.addScalar("TODOUFUKEN", Hibernate.STRING);
        query.addScalar("ADDR1", Hibernate.STRING);
        query.addScalar("SHIMEI", Hibernate.STRING);
        query.addScalar("TEL", Hibernate.STRING);
        query.addScalar("TODOKESAKI_IND", Hibernate.STRING);
        query.addScalar("SHINKISAKUSEI_DATE", Hibernate.STRING);
        query.addScalar("SAKUJO_FLG", Hibernate.STRING);
        query.addScalar("CHUUI1", Hibernate.STRING);
        query.addScalar("CHUUI2", Hibernate.STRING);
        query.addScalar("CHUUI3", Hibernate.STRING);

        return query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP).list();
    }

    /**
     * Static condition to retrieve black kokyaku
     * 
     * @return FROM phrase and WHERE phrase of sql
     */
    private static String getBlackKokyakuStaticCondition () {

        StringBuilder sql = new StringBuilder();

        // sysdate - (365 * 3);
        Calendar calendar = DateUtil.getCalendar();
        calendar.add(Calendar.DAY_OF_MONTH, 365 * -3);

        sql.append(" FROM ");
        sql.append("     m_kokyaku t1");
        sql.append("     INNER JOIN ");
        sql.append("         m_todokesaki t2 ");
        sql.append("         ON (t1.kokyaku_id = t2.kokyaku_id) ");
        sql.append("     INNER JOIN ");
        sql.append("         m_todoufuken t3 ");
        sql.append("         ON (t2.todoufuken_id = t3.todoufuken_id) ");

        sql.append(" WHERE ");
        sql.append("    t1.sakujo_flg = 0 ");
        sql.append("    AND t2.shinkisakusei_date >= TO_DATE('")
            .append(DateUtil.toString(calendar.getTime(), "yyyy/MM/dd HH:mm:ss"))
            .append("','yyyy/MM/dd hh24:mi:ss')");

        return sql.toString();
    }

    /**
     * 
     * @param staticSql Static condition to retrieve black kokyaku
     * @param conditionList List of condition item used to acquire
     * @return Hibernate SQLQuery object
     */
    private static SQLQuery getBlackKokyakuSqlQuery(
            String staticSql,
            List<BlackKokyakuCondition> conditionList) {

        StringBuilder sqlCondition = new StringBuilder();
        HashMap<String, String> nameStringValuePairs = new HashMap<String, String>();
        HashMap<String, Integer> nameIntegerValuePairs = new HashMap<String, Integer>();
        int rowNo = 1;

        for (BlackKokyakuCondition condition : conditionList) {
            if (condition.isAllItemEmpty()) {
                continue;
            }

            StringBuilder sqlConditionLine = new StringBuilder();

            if (!StringUtil.isNullOrEmpty(condition.getTel())) {
                sqlConditionLine
                        .append(" (TRIM(REPLACE(T2.TEL, '-', '')) = :tel_")
                        .append(rowNo)
                        .append(") ");

                nameStringValuePairs.put("tel_" + rowNo, condition.getTel());
            }

            if (!StringUtil.isNullOrEmpty(condition.getShimei())) {
                if (sqlConditionLine.length() > 0) {
                    sqlConditionLine.append(" AND ");
                }
                sqlConditionLine.append(" (T2.SHIMEI LIKE :shimei_")
                        .append(rowNo)
                        .append(") ");

                nameStringValuePairs.put("shimei_" + rowNo,
                        condition.getShimei());
            }

            if (!condition.isSelectedDefaultTodoufukenId()) {
                if (sqlConditionLine.length() > 0) {
                    sqlConditionLine.append(" AND ");
                }
                sqlConditionLine.append(" (T3.TODOUFUKEN_ID = :todoufukenId_")
                        .append(rowNo)
                        .append(") ");

                nameIntegerValuePairs.put("todoufukenId_" + rowNo,
                        condition.getTodoufukenId());
            }

            if (!StringUtil.isNullOrEmpty(condition.getAddr1())) {
                if (sqlConditionLine.length() > 0) {
                    sqlConditionLine.append(" AND ");
                }
                sqlConditionLine.append(" (UPPER(TO_MULTI_BYTE(T2.ADDR1)) ")
                        .append("LIKE UPPER(TO_MULTI_BYTE(:addr1_")
                        .append(rowNo)
                        .append("))) ");

                nameStringValuePairs
                        .put("addr1_" + rowNo, condition.getAddr1());
            }

            if (rowNo > 1) {
                sqlCondition.append(" OR ");
            }
            sqlCondition.append(" ( ")
                    .append(sqlConditionLine.toString())
                    .append(" ) ");

            rowNo++;
        }

        StringBuilder sql = new StringBuilder();

        // The condition of making it for static SQL is added.
        sql.append(staticSql);
        if (sqlCondition.length() > 0) {
            sql.append("    AND ( ")
                    .append(sqlCondition.toString())
                    .append("    ) ");
        }

        EntityManager em = EntityManagerFactory.getEntityManager();
        SQLQuery query = em.createSQLQuery(sql.toString());

        for (Map.Entry<String, String> entry : nameStringValuePairs.entrySet()) {
            query.setString(entry.getKey(), entry.getValue());
        }

        for (Map.Entry<String, Integer> entry : nameIntegerValuePairs
                .entrySet()) {
            query.setInteger(entry.getKey(), entry.getValue());
        }

        return query;
   }
}